const express = require("express");
const router = express.Router();
const moment = require("moment");
const { connection } = require("../util/mysql");
const fs = require("fs");
const path = require("path");
//文件插件

let uploadURL = path.join(__dirname, "/upload");
//开放
router.use(express.static(uploadURL));

//引入others
const otherIndex = require("./others");
router.use(otherIndex);
const { local } = require("../url");

var mysql = require("mysql");
const env = {
  host: "111.231.22.97",
  user: "wangs-web",
  password: "HsTjw82zF7f6fjWY",
  database: "wangs-web", //数据库名字
};

//过滤
const filters = (e) => {
  let objs = {};
  for (let keys in e) {
    if (e[keys] != "" && e[keys] != undefined && e[keys] != null) {
      objs[keys] = e[keys];
    }
  }
  return Object.keys(objs);
};

//获取
const gets = (req, res) => {
  let arrs = filters(req.query);
  let pageNo = Number(req.query["pageNo"]);
  let pageSize = Number(req.query["pageSize"]);
  let offset = (pageNo - 1) * pageSize;
  console.log(offset);
  console.log(arrs, "arrs");
  let pools = mysql.createPool(env);
  if (arrs.length == 2) {
    console.log("走全部查询");
    let sql = "select * from projectlist limit ?,?";
    let sqls = "select count(*) from projectlist";
    pools.query(sql, [offset, pageSize], (err, result) => {
      if (err) throw err;
      //返回信息
      //获取总数
      pools.query(sqls, (err, total) => {
        if (err) throw err;
        res.send({
          code: 200,
          msg: "success",
          data: {
            data: result,
            total: total[0]["count(*)"],
          },
        });
      });
    });
  } else {
    console.log("走子查询");
    let target = filters(req.query).filter((res) => {
      return res != "pageNo" && res != "pageSize";
    });
    let newArr = [];
    let sql = "select * from projectlist where ";
    let sqls = "select count(*) from projectlist where";
    target.forEach((ele, index) => {
      newArr.push(req.query[ele]);
      if (index != target.length - 1) {
        sql = sql + ` ${ele} = ? and `;
        sqls = sqls + ` ${ele} = ? and `;
      } else {
        sql = sql + ` ${ele} = ? limit ?,?`;
        sqls = sqls + ` ${ele} = ? `;
      }
    });
    newArr.push(offset, pageSize);
    pools.query(sql, newArr, (err, result) => {
      if (err) throw err;
      //返回数据
      pools.query(sqls, newArr, (err, total) => {
        res.send({
          code: 200,
          msg: "success",
          data: {
            data: result,
            total: total[0]["count(*)"],
          },
        });
      });
    });
  }
};

router.get("/getProjectlist", (req, res) => {
  gets(req, res);
});

//新增

//新增projectID
const indexs = () => {
  let str = "";
  for (let i = 0; i < 10; i++) {
    let ind = Math.ceil(Math.random() * 10);
    str += ind + 1;
  }
  return str;
};

const status = require("../status/status");
const multer = require("multer");
const URL = require("../url");
const { append, set } = require("express/lib/response");
const res = require("express/lib/response");
const { resolve } = require("path");
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, URL.projectURL);
  },
  filename: (req, file, cb) => {
    console.log(file, "file");
    file.originalname = Buffer.from(file.originalname, "latin1").toString(
      "utf-8"
    );
    console.log(file.originalname, "6666");
    cb(null, file.originalname);
  },
});

const upload = multer({ storage: storage });
router.post("/postProjectURL", upload.single("file"), async (req, res) => {
  if (req.file) {
    res.send({
      code: 200,
      msg: "success",
      data: {
        url: req.file.originalname,
      },
    });
    return;
  }
  res.send(status.errs());
});

//新增
router.post("/postProject", (req, res) => {
  let pools = mysql.createPool(env);
  let sql = "insert into projectlist values(?,?,?,?,?,?,?,?)";
  const { projectName, projectPerson, startTime, endTime, price, projectURL } =
    req.body;
  const id = "ws" + indexs();
  const arr = [
    id,
    projectName,
    projectPerson,
    startTime,
    endTime,
    price,
    projectURL,
    "0",
  ];
  pools.query(sql, arr, (err, result) => {
    if (err) throw err;
    if (result.affectedRows == 1) {
      let sqll = "insert into projectdetail values(?,?)";
      const arrl = [id, req.body.member];
      pools.query(sqll, arrl, (err, result) => {
        if (err) throw err;
        if (result.affectedRows == 1) {
          res.send({
            code: 200,
            msg: "success",
          });
        }
      });
    }
  });
});

//删除
const deletes = (req, res) => {
  console.log(req.query)
  //可以续加
  let sql = ''
  /* 0的时候dtotal表未加入 所以(projectlist)*/
  if (req.query.status == '0') {
    sql =
      "delete projectlist,projectdetail from projectlist inner join projectdetail on projectlist.projectCode = projectdetail.projectCode where projectlist.projectCode=?";
  }
  else if (req.query.status == '1') {
    sql = "delete projectlist,projectdetail,dtotallist from projectlist inner join projectdetail on projectlist.projectCode = projectdetail.projectCode inner join dtotallist on projectlist.projectCode=dtotallist.projectCode  where projectlist.projectCode=?";
  }
  console.log(sql, 'sql')




  let pools = mysql.createPool(env);
  pools.query(sql, [req.query.code], (err, result) => {
    if (err) throw err;
    console.log(result, "result");
    if (result.affectedRows >= 1) {
      res.send({
        code: 200,
        msg: "success",
      });
      return;
    } else {
      res.send(status.errs());
    }
  });
};

router.get("/deleteProject", (req, res) => {
  deletes(req, res);
});

//查询成员、管理员

const returnUser = (str, arr) => {
  let arrs = str.split("/");
  console.log(arrs, "arrs");
  let res = [];
  arrs.forEach((ele) => {
    arr.forEach((el) => {
      if (ele == el.id) {
        let obj = {
          name: el.name,
          role: el.role,
        };
        res.push(obj);
      }
    });
  });
  return res;
};

const searchUser = (req, res) => {
  let pools = mysql.createPool(env);
  let sql = ''
  req.query.mode == '1' ? sql = "select * from projectdetail where projectCode=?" : sql = 'select * from projectdraf where projectCode=?'
  let { code } = req.query;
  pools.query(sql, [code], (err, result) => {
    if (err) throw err;
    if (result.length > 0) {
      let str = result[0].member;
      let sqls = "select * from userlist";
      pools.query(sqls, (err, rel) => {
        if (err) throw err;
        console.log(rel, "rep");
        const data = returnUser(str, rel);
        res.send({
          code: 200,
          msg: "success",
          data: data,
        });
      });
    } else {
      res.send({
        code: 200,
        msg: "success",
        data: [],
      });
    }
  });
};
router.get("/searchUser", (req, res) => {
  searchUser(req, res);
});

//查询所有成员
router.get("/allUser", (req, res) => {
  let pools = mysql.createPool(env);
  let sql = "select * from userlist";
  pools.query(sql, (err, result) => {
    if (err) throw err;
    console.log(result,'result')
    res.send({
      code: 200,
      msg: "success",
      data: result,
    });
  });
});

//确认 0前期准备 1开发中 2已完成

const getDays = (date1, date2) => {
  const oneDay = 24 * 60 * 60 * 1000; // 一天的毫秒数
  const time1 = new Date(date1).getTime(); // 第一个日期的时间戳
  const time2 = new Date(date2).getTime(); // 第二个日期的时间戳
  const difference = Math.abs(time1 - time2); // 两个日期的差值（毫秒数）
  const days = Math.floor(difference / oneDay); // 将差值转换为天数
  return days;
};

const dtotals = (req, res) => {
  return new Promise((resolve, reject) => {
    let sql = "select * from projectlist where projectCode = ?";
    let pools = mysql.createPool(env);
    let queryArr = [req.body["code"]];
    pools.query(sql, queryArr, (err, result) => {
      if (err) {
        res.status(500);
      }
      let days = getDays(result[0].startTime, result[0].endTime);
      resolve(days);
    });
  });
};

const length = (req) => {
  return new Promise((resolve, reject) => {
    let sql = "select * from projectdetail where projectCode = ?";
    let pools = mysql.createPool(env);
    pools.query(sql, [req.body["code"]], (err, resl) => {
      resolve(resl[0].member.split("/").length);
    });
  });
};

//判断有无才新增

const deterInsert = (req) => {
  return new Promise((resolve, reject) => {
    let pools = mysql.createPool(env);
    let sql = "select * from dtotallist where projectCode = ?";
    pools.query(sql, [req.body["code"]], (err, result) => {
      if (result.length == 0) {
        resolve(true);
      } else {
        resolve(false);
      }
    });
  });
};

const accepts = (req, res) => {
  let sql = "update projectlist set projectStatus=? where projectCode=?";
  let pools = mysql.createPool(env);
  pools.query(sql, [req.body["status"], req.body["code"]], (err, result) => {
    if (err) throw err;
    if (result.affectedRows == 1) {
      /* 查看成员 */
      let sqls = "select * from  projectdetail where projectCode = ?";
      let arrs = [req.body["code"]];
      pools.query(sqls, arrs, async (err, rel) => {
        if (err) {
          res.send({
            code: 500,
            msg: "error",
          });
          return;
        }
        let member = rel[0].member;
        let a = await dtotals(req, res);
        let b = await length(req);
        //计算总条目数
        let dtotales = a * b;
        const { code, projectName } = req.body;
        const intoArr = [code, projectName, member, dtotales, a, "1", "0"];
        let deter = await deterInsert(req);
        console.log(deter, "555");
        if (deter) {
          let sqles = "insert into dtotallist values (?,?,?,?,?,?)";
          pools.query(sqles, intoArr, (err, rek) => {
            if (err) {
              res.send({
                code: 500,
                msg: "error",
              });
              return;
            }
            if (rek.affectedRows == 1) {
              res.send({
                code: 200,
                msg: "success",
              });
            }
          });
          return;
        }
        res.send({
          code: 500,
          msg: "该项目已存在",
        });
      });
    } else {
      res.send({
        code: 500,
        msg: "error",
      });
    }
  });
};
//验收
const checkAcceptance = (req, res) => {
  let projectStatus = req.body.status;
  let projectCode = req.body.code;
  let arr = [projectStatus, projectCode];
  let sql = "update projectlist set projectStatus = ? where projectCode = ?";
  let pools = mysql.createPool(env);
  pools.query(sql, arr, (err, result) => {
    if (err) {
      console.log(err + "err");
      res.status(500);
    } else {
      if (result.affectedRows >= 1) {
        res.send({
          code: 200,
          msg: "success",
        });
        return;
      }
      res.send({
        code: 500,
        msg: "修改状态失败",
      });
    }
  });
};
router.post("/updateProjectStatus", (req, res) => {
  if (req.body.status == "1") {
    accepts(req, res);
  } else {
    checkAcceptance(req, res);
  }
});

//获取单条信息
const returnUsers = (str, arr) => {
  let arrs = str.split("/");
  console.log(arrs, "arrs");
  let res = [];
  arrs.forEach((ele) => {
    arr.forEach((el) => {
      if (ele == el.id) {
        let obj = {
          name: el.name,
          role: el.role,
          id: el.id,
        };
        res.push(obj);
      }
    });
  });
  return res;
};
const getSinge = (req, res) => {
  let sql =
    "select * from   projectlist inner join projectdetail on projectlist.projectCode = projectdetail.projectCode where projectlist.projectCode = ?";
  let pools = mysql.createPool(env);
  pools.query(sql, [req.params["code"]], (err, result) => {
    if (err) throw err;
    console.log(result);
    if (result) {
      let sqls = "select * from userlist";
      pools.query(sqls, (err, rel) => {
        if (err) throw err;
        const datas = returnUsers(result[0].member, rel);
        result[0].memberArr = datas;
        res.send({
          code: 200,
          msg: "success",
          data: result[0],
        });
      });
    } else {
      res.send({
        code: 500,
        msg: "success",
      });
    }
  });
};

router.get("/getSingeProject/:code", (req, res) => {
  getSinge(req, res);
});

//编辑

//dtotal表修改

//时间修改的情况下
const timeUpdate = (req) => {
  return new Promise((reslove, reject) => {
    let pools = mysql.createPool(env);
    pools.query(
      "select startTime,endTime from projectlist where projectCode = ?",
      [req.body.projectCode],
      (err, result) => {
        reslove(result);
      }
    );
  });
};

const updatedtotal = async (req, res) => {
  //时间修改——修改个人的时间
  const obj = await timeUpdate(req);
  let mtotales = getDays(obj[0].startTime, obj[0].endTime);
  console.log(mtotales, "iii");
  //
  let pools = mysql.createPool(env);
  let sql =
    "update dtotallist set projectName = ? , member = ? ,mtotals = ? where projectCode = ?";
  let queryArr = [
    req.body.projectName,
    req.body.member,
    mtotales,
    req.body.projectCode,
  ];
  pools.query(sql, queryArr, (err, result) => {
    if (err) {
      console.log(err + "err");
    } else {
      if (result.affectedRows >= 1) {
        /* 查表看看成员 */
        let sqles =
          "select member,mtotals from dtotallist where projectCode = ? ";
        pools.query(sqles, [req.body.projectCode], (err, resk) => {
          if (err) throw err;
          const mtotals = Number(resk[0]["mtotals"]);
          const nums = resk[0]["member"].split("/").length;
          const dtotals = nums * mtotals;
          console.log(mtotals, "2222");
          console.log(dtotals, "11111");
          let updateSql =
            "update dtotallist set dtotals = ?, mtotals = ? where projectCode = ?";
          pools.query(
            updateSql,
            [dtotals, mtotals, req.body.projectCode],
            (err, resp) => {
              if (err) throw err;
              else {
                if (resp.affectedRows == 1) {
                  res.send({
                    code: 200,
                    msg: "success",
                  });
                } else {
                  res.send({
                    code: 500,
                    msg: "error",
                  });
                }
              }
            }
          );
        });
      } else {
        res.send({
          code: 500,
          msg: "error",
        });
      }
    }
  });
};

const editProject = (req, res) => {
  let pools = mysql.createPool(env);
  let sql =
    "update projectlist  set projectName=?,projectPerson=?,startTime=?,endTime=?,price=?,projectURL=?,projectStatus=? where projectCode=?";
  const {
    projectName,
    projectPerson,
    startTime,
    endTime,
    price,
    projectURL,
    projectStatus,
    projectCode,
  } = req.body;
  const arr = [
    projectName,
    projectPerson,
    startTime,
    endTime,
    price,
    projectURL,
    projectStatus,
    projectCode,
  ];
  pools.query(sql, arr, (err, result) => {
    if (err) throw err;
    if (result.affectedRows == 1) {
      let sql = "update projectdetail set member = ? where projectCode = ?";
      const { member } = req.body;
      pools.query(sql, [member, projectCode], (err, rel) => {
        if (err) throw err;
        // res.send({
        //   code: 200,
        //   msg: "success",
        // });
        updatedtotal(req, res);
      });
    } else {
      res.send({
        code: 500,
        msg: "error",
      });
    }
  });
};

const noDevelopment = (req, res) => {
  const {
    projectCode,
    projectName,
    projectPerson,
    startTime,
    endTime,
    price,
    projectURL,
    projectStatus,
  } = req.body;
  const arr = [
    projectName,
    projectPerson,
    startTime,
    endTime,
    price,
    projectURL,
    projectStatus,
    projectCode,
  ];
  let sql = "update projectlist set ";
  const arrs = Object.keys(req.body).filter((res) => {
    return res != "member" && res != "memberArr" && res != "projectCode";
  });
  arrs.forEach((ele, index) => {
    if (index < arrs.length - 1) {
      sql += ele + " = ? , ";
    } else {
      sql += ele + " = ?  where projectCode = ?";
    }
  });
  console.log(sql, "sql");
  console.log(arr, "arr");
  let pools = mysql.createPool(env);
  pools.query(sql, arr, (err, result) => {
    if (err) {
      res.status(500);
      return;
    }
    if (result.affectedRows == 1) {
      // res.send({
      //   code: 200,
      //   msg: "success",
      // });
      //修改projectdetail
      let sqls = "update projectdetail set member = ? where projectCode = ?";
      pools.query(
        sqls,
        [req.body.member, req.body.projectCode],
        (err, resk) => {
          if (err) {
            res.send(500);
          } else {
            if (resk.affectedRows >= 0) {
              res.send({
                code: 200,
                msg: "success",
              });
            } else {
              res.send({
                code: 500,
                msg: "修改失败",
              });
            }
          }
        }
      );
    } else {
      res.send({
        code: 500,
        msg: "修改失败",
      });
    }
  });
};

router.post("/updateSingProject", (req, res) => {
  //开发中修改
  if (req.body.projectStatus == "1") {
    editProject(req, res);
  } else {
    noDevelopment(req, res);
  }
});

/* uni中查询 */
const uniGetProjectlist = (req, res) => {
  let pools = mysql.createPool(env)
  const arrs = filters(req.query)
  if (arrs.length == 0) {
    console.log('全部查询')
    let sql = 'select * from projectlist'
    pools.query(sql, [], (err, result) => {
      if (err) {
        res.status(500)
        console.log(err, 'error')
      }
      res.send({
        code: 200,
        msg: 'success',
        data: {
          data: result
        }
      })
    })
  }
  else {
    let sql = 'select * from projectlist where '
    let arr = ['projectName', 'projectPerson']
    let queryArr = []
    console.log(req.query.key)
    arr.forEach((el, ind) => {
      queryArr.push(req.query.key)
      if (ind != arr.length - 1) {
        sql += `${el} like ` + `'%${req.query.key}%' or `
      }
      else {
        sql += `${el} like ` + `'%${req.query.key}%' `
      }
    })
    pools.query(sql, queryArr, (err, resl) => {
      if (err) {
        res.status(500)
        console.log(err, 'error')
      }
      res.send({
        code: 200,
        msg: 'success',
        data: {
          data: resl
        }
      })
    })
  }
}
router.get('/uniGetProjectlist', (req, res) => {
  uniGetProjectlist(req, res)
})

/* 查询成员 */
const uniGetUserlist = (req, res) => {
  console.log(req.query)
  let pools = mysql.createPool(env)
  let sql = 'select * from userlist'
  pools.query(sql, [], (err, resl) => {
    if (err) {
      res.status(500)
      console.log(err, 'error')
    }
    let arr = []
    resl.forEach(ele => {
      let obj = {
        value: ele.id,
        name: ele.name
      }
      arr.push(obj)
    })
    let sql = 'select * from projectdetail where projectCode = ?'
    pools.query(sql, [req.query.projectCode], (err, resk) => {
      if (err) {
        res.status(500)
        console.log(err, 'error')
      }
      if (resk[0]) {
        let arrs = []
        arrs = arrs.concat(resk[0].member.split('/'))
        res.send({
          code: 200,
          msg: 'success',
          data: {
            data: arr,
            check: arrs
          }
        })
      }
      else {
        res.send({
          code: 500,
          msg: 'error',
        })
      }
    })


    // res.send({
    //   code:200,
    //   msg:'success',
    //   data:{
    //     data:arr
    //   }
    // })
  })
}
router.get('/uniGetUserlist', (req, res) => {
  uniGetUserlist(req, res)
})


/* uni 编辑信息 */

//member修改的情况下
const doubleDaily = (req, res, pools) => {
  console.log('111')
  let nums = req.body.member.split('/').length
  let days = getDays(req.body.startTime, req.body.endTime)
  let sql = 'update dtotallist set member = ?,dtotals = ?,mtotals=? where projectCode = ?'
  let dtotales = days * nums
  let mtotales = days
  let queryArr = [req.body.member, dtotales, mtotales, req.body.projectCode]
  pools.query(sql, queryArr, (err, result) => {
    if (err) {
      res.status(500)
      console.log(err, 'error')
    }
    else {
      if (result.affectedRows >= 1) {
        res.send({
          code: 200,
          msg: 'success'
        })
      }
      else {
        res.send({
          code: 500,
          msg: 'error'
        })
      }
    }
  })
}

const postDoblue = (req, res) => {
  console.log('2222')
  let pools = mysql.createPool(env)
  let { projectName, projectPerson, startTime, endTime, price, member, projectCode } = req.body
  const arrs = [
    {
      name: 'projectName',
      form: 'projectlist'
    },
    {
      name: 'projectPerson',
      form: 'projectlist'
    },
    {
      name: 'startTime',
      form: 'projectlist'
    },
    {
      name: 'endTime',
      form: 'projectlist'
    },
    {
      name: 'price',
      form: 'projectlist'
    },
    {
      name: 'member',
      form: 'projectdetail'
    }
  ]
  let queryArr = [projectName, projectPerson, startTime, endTime, price, member, projectCode]
  let sql = 'update projectlist join projectdetail on projectlist.projectCode=projectdetail.projectCode set '
  arrs.forEach((ele, ind) => {
    if (ind != arrs.length - 1) {
      sql += ` ${ele.form}.${ele.name} = ? , `
    }
    else {
      sql += ` ${ele.form}.${ele.name} = ? where projectlist.projectCode = ?`
    }
  })
  console.log(sql, 'sql')
  pools.query(sql, queryArr, (err, resp) => {
    if (err) {
      res.status(500)
      console.log(err, 'error')
    }
    else {
      if (resp.affectedRows >= 1) {
        // res.send({
        //   code:200,
        //   msg:'success'
        // })
        //计算日报
        doubleDaily(req, res, pools)
      }
      else {
        res.send({
          code: 500,
          msg: 'error'
        })
      }
    }
  })
}

//计算日报周报
const singDaily = (req, res, pools) => {
  const { startTime, endTime, projectCode } = req.body
  //1.先查询member
  pools.query('select member,dtotals,mtotals from dtotallist where projectCode = ?', [req.body.projectCode], (err, result) => {
    if (err) {
      res.status(500)
      console.log(err, 'error')
    }
    let days = getDays(startTime, endTime)
    console.log(days, 'days')
    let nums = result[0].member.split('/').length
    let dtotales = nums * days
    let mtotales = days
    let sqls = 'update dtotallist set dtotals=?,mtotals=? where projectCode=?'
    pools.query(sqls, [dtotales, mtotales, projectCode], (err, resp) => {
      if (err) {
        res.status(err)
        console.log(err, 'error')
      }
      else {
        if (resp.affectedRows >= 1) {
          res.send({
            code: 200,
            msg: 'success'
          })
        }
        else {
          res.send({
            code: 500,
            msg: 'error'
          })
        }
      }
    })
  })
}

//member无修改
const PostSing = (req, res) => {
  let pools = mysql.createPool(env)
  let { projectName, projectPerson, startTime, endTime, price, projectCode } = req.body
  let queryArr = [projectName, projectPerson, startTime, endTime, price, projectCode]
  const arrs = [
    {
      name: 'projectName',
      form: 'projectlist'
    },
    {
      name: 'projectPerson',
      form: 'projectlist'
    },
    {
      name: 'startTime',
      form: 'projectlist'
    },
    {
      name: 'endTime',
      form: 'projectlist'
    },
    {
      name: 'price',
      form: 'projectlist'
    },
  ]
  let sql = 'update projectlist  set '
  arrs.forEach((ele, ind) => {
    if (ind != arrs.length - 1) {
      sql += ` ${ele.form}.${ele.name} = ? , `
    }
    else {
      sql += ` ${ele.form}.${ele.name} = ? where projectlist.projectCode = ?`
    }
  })
  pools.query(sql, queryArr, (err, resp) => {
    if (err) {
      res.status(500)
      console.log(err, 'error')
    }
    else {
      if (resp.affectedRows >= 1) {
        //计算日报
        singDaily(req, res, pools)
      }
      else {
        res.send({
          code: 500,
          msg: 'error'
        })
      }
    }
  })
}




const uniPostProject = (req, res) => {
  if (req.body.member) {
    postDoblue(req, res)
  }
  else {
    PostSing(req, res)
  }
}
router.post('/uniPostProject', (req, res) => {
  uniPostProject(req, res)
})




module.exports = router;
